﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Runda.EShop.Common.Utility;
using Runda.EShop.Common.Entity;
using System.Text;

namespace Runda.EShop.Dal
{
    /// <summary>
    /// DAL layer class dbo.Bas_Product.
    /// </summary>
    public partial class BasProductDal 
    {
        public void ProductSync()
        {
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand("usp_ProductSync");
            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        public void EntAddrSync()
        {
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand("usp_EntAddrSync");
            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }


        public DataTable GetProductListByCate(string CateCode, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sql = @"select P.*,ISNULL(S.Quantity,0) as Quantity,CASE WHEN S.Quantity IS NULL then 0 else 1 end AS IsAddedCart from Bas_Product P LEFT JOIN Biz_ShopCart S ON P.ProductID=S.ProductID where P.CateCode in (select CateCode from Bas_Product_Cate where CateCode like @CateCode) or  P.CateCodeEx in (select CateCode from Bas_Product_Cate where CateCode like @CateCode)";
            StringBuilder sqlSb = new StringBuilder(sql); 

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sql, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@CateCode", DbType.String, CateCode+"%");

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetProductListByCate(string CateCode, int CateType, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sql;
            if (CateType == 1) { sql = @"select P.*,ISNULL(S.Quantity,0) as Quantity,CASE WHEN S.Quantity IS NULL then 0 else 1 end AS IsAddedCart from Bas_Product P LEFT JOIN Biz_ShopCart S ON P.ProductID=S.ProductID where P.CateCode in (select CateCode from Bas_Product_Cate where CateCode like @CateCode)"; }
            else { sql = @"select P.*,ISNULL(S.Quantity,0) as Quantity,CASE WHEN S.Quantity IS NULL then 0 else 1 end AS IsAddedCart from Bas_Product P LEFT JOIN Biz_ShopCart S ON P.ProductID=S.ProductID where P.CateCodeEx in (select CateCode from Bas_Product_Cate where CateCode like @CateCode)"; }
            
            StringBuilder sqlSb = new StringBuilder(sql);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sql, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@CateCode", DbType.String, CateCode + "%");

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetProductListByCustomer(Guid CustomerID, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sql = @"select P.*,ISNULL(S.Quantity,0) as Quantity,CASE WHEN S.Quantity IS NULL then 0 else 1 end AS IsAddedCart from Bas_Product P LEFT JOIN Biz_ShopCart S ON P.ProductID=S.ProductID where P.CateCode in (select CateCode from Relation_Customer_ProductCate where CustomerID = @CustomerID) or P.CateCodeEx in (select CateCode from Relation_Customer_ProductCate where CustomerID = @CustomerID)";
            StringBuilder sqlSb = new StringBuilder(sql);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sql, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@CustomerID", DbType.Guid, CustomerID);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetProductListByCustomer(Guid CustomerID,int CateType, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sql = "";
            if (CateType == 1) { sql = @"select P.*,ISNULL(S.Quantity,0) as Quantity from Bas_Product P LEFT JOIN Biz_ShopCart S ON P.ProductID=S.ProductID where P.CateCode in (select CateCode from Relation_Customer_ProductCate where CustomerID = @CustomerID)"; }
            else { sql = @"select P.*,ISNULL(S.Quantity,0) as Quantity from Bas_Product P LEFT JOIN Biz_ShopCart S ON P.ProductID=S.ProductID where P.CateCodeEx in (select CateCode from Relation_Customer_ProductCate where CustomerID = @CustomerID)"; }
            
            StringBuilder sqlSb = new StringBuilder(sql);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sql, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@CustomerID", DbType.Guid, CustomerID);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetProductListByKey(string Key, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sql = @"select * from Bas_Product P  where ProductName LIKE @Key1 OR ProductCode = @Key2 or Alias= LIKE @Key1";
            StringBuilder sqlSb = new StringBuilder(sql);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sql, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@Key1", DbType.String, "%" + Key + "%");
            CurrentDatabase.AddInParameter(dbCommand, "@Key2", DbType.String, Key);


            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetProductListByKey(string Key,string CustomerID, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sql = @"select * from Bas_Product P  where (ProductName LIKE @Key1 OR ProductCode = @Key2 or Alias= LIKE @Key1) and ProductID in(select ProductID from Relation_Customer_Product where customerid = @CustomerID)";
            //and (CateCode in(select CateCode from Relation_Customer_ProductCate  where customerid = @CustomerID) or CateCodeEx in(select CateCode from Relation_Customer_ProductCate where customerid = @CustomerID)) ";
            StringBuilder sqlSb = new StringBuilder(sql);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sql, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@Key1", DbType.String, "%" + Key + "%");
            CurrentDatabase.AddInParameter(dbCommand, "@Key2", DbType.String, Key);
            CurrentDatabase.AddInParameter(dbCommand, "@CustomerID", DbType.String, CustomerID);


            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
        public DataTable GetOldestProductInventory(string ProductCode)
        {
            string sql = @"if EXISTS (select   * FROM [vw_WMS_skuPatch] WHERE SKU=@ProductCode)
BEGIN
select top (3) * FROM [vw_WMS_skuPatch] WHERE SKU=@ProductCode ORDER BY DueDate ASC
END
ELSE
BEGIN
SELECT NEWID() as OID,@ProductCode as sku,NULL as BatchCode,NULL as DueDate,0 quantity
END
";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ProductCode", DbType.String, ProductCode);
            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        public DataTable GetOldestProductInventory(string Company ,string ProductCode)
        {
            string sql = @"if EXISTS (select   * FROM [vw_WMS_skuPatch] WHERE SKU=@ProductCode and Company = @Company)
BEGIN
select * FROM [vw_WMS_skuPatch] WHERE SKU=@ProductCode and Company = @Company ORDER BY DueDate ASC
END
ELSE
BEGIN
SELECT NEWID() as OID,@Company as Company,@ProductCode as sku,NULL as BatchCode,NULL as DueDate,0 quantity
END
";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ProductCode", DbType.String, ProductCode);
            CurrentDatabase.AddInParameter(dbcommand, "@Company", DbType.String, Company);
            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        //取保留库存
        public DataTable GetHoldProductStock(string EntCode, string ProductCode)
        {
            string sql = @"select  * FROM [vw_WMS_Stock_customer] WHERE SKU=@ProductCode and EntCode =@EntCode ORDER BY DueDate ASC";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ProductCode", DbType.String, ProductCode);
            CurrentDatabase.AddInParameter(dbcommand, "@EntCode", DbType.String, EntCode);
            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }
        public DataTable GetHoldProductStock(string CustomerID,string EntCode, string ProductCode)
        {
            string sql = @"select  * FROM [vw_WMS_Stock_Remain] WHERE SKU=@ProductCode and customerid =@CustomerID and customercode=@EntCode ORDER BY DueDate ASC";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ProductCode", DbType.String, ProductCode);
            CurrentDatabase.AddInParameter(dbcommand, "@CustomerID", DbType.String, CustomerID);
            CurrentDatabase.AddInParameter(dbcommand, "@EntCode", DbType.String, EntCode);
            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        public DataTable QueryProduct(CustomerProductQueryConditionEntity Condition, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sql = "";
            if (Condition.CateType == 1) 
            {
                // and P.CateCode in (select CateCode from Relation_Customer_ProductCate where CustomerID = @CustomerID)
                sql = @"SELECT P.* FROM Bas_Product P where P.ProductType=1 AND P.ProductID IN (SELECT ProductID FROM Relation_Customer_Product WHERE CustomerID = @CustomerID)"; 
            }
            else 
            {
                sql = @"SELECT P.* FROM Bas_Product P where P.ProductType=1  AND P.ProductID IN (SELECT ProductID FROM Relation_Customer_Product WHERE CustomerID = @CustomerID)"; 
            }
            if (Condition.InstrumentID.HasValue)
            {
                if (Condition.InstrumentID.ToString() == "11111111-1111-1111-1111-111111111111")
                {
                    sql += " and P.ProductID not IN (SELECT ReagentID FROM Relation_Instrument_Reagent)";
                }
                else
                {
                    sql += " and P.ProductID IN (SELECT ReagentID FROM Relation_Instrument_Reagent WHERE InstrumentID=@InstrumentID)";
                }
            }
            if (!string.IsNullOrEmpty(Condition.CateCode))
            {
                if (Condition.CateType == 1)
                {
                    sql += " and P.CateCode LIKE @CateCode"; 
                }
                else
                {
                    sql += " and P.CateCodeEx LIKE @CateCode";
                }
            }

            if (!string.IsNullOrEmpty(Condition.Trademark))
            {
                sql += " and P.Trademark = @Trademark";
            }

            if (!string.IsNullOrEmpty(Condition.Key))
            {
                sql += " and (P.ProductName LIKE @Key1 OR P.ProductCode = @Key2 or P.Alias like @Key1 )";
            }


            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sql, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@CustomerID", DbType.Guid, Condition.Customer.UserID);
            if (Condition.InstrumentID.HasValue)
            {
                CurrentDatabase.AddInParameter(dbCommand, "@InstrumentID", DbType.Guid, Condition.InstrumentID.Value);
            }
            if (!string.IsNullOrEmpty(Condition.CateCode))
            {
                CurrentDatabase.AddInParameter(dbCommand, "@CateCode", DbType.String, Condition.CateCode + "%");
            }

            if (!string.IsNullOrEmpty(Condition.Trademark))
            {
                CurrentDatabase.AddInParameter(dbCommand, "@Trademark", DbType.String, Condition.Trademark);
            }

            if (!string.IsNullOrEmpty(Condition.Key))
            {
                CurrentDatabase.AddInParameter(dbCommand, "@Key1", DbType.String, "%"+Condition.Key + "%");
                CurrentDatabase.AddInParameter(dbCommand, "@Key2", DbType.String, "%" + Condition.Key + "%"); 
            }

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        //产品品牌
        public DataTable QueryProductTrademark(Guid Customer, Guid? InstrumentID, string CateCode, int CateType)
        {
            string sql = "";
            sql = @"SELECT distinct P.Trademark FROM Bas_Product P where P.ProductType!=2 AND P.ProductID IN (SELECT ProductID FROM Relation_Customer_Product WHERE CustomerID = @CustomerID) and Trademark is not null";

            if (InstrumentID.HasValue)
            {
                if (InstrumentID.Value.ToString() != "11111111-1111-1111-1111-111111111111")
                {
                    sql += " and P.ProductID IN (SELECT ReagentID FROM Relation_Instrument_Reagent WHERE InstrumentID=@InstrumentID)";
                }
                else
                { sql += " and P.ProductID not IN (SELECT ReagentID FROM Relation_Instrument_Reagent)"; }
            }
            if (!string.IsNullOrEmpty(CateCode))
            {
                sql += " and (P.CateCodeEx LIKE @CateCode)";
            }

            sql += " order by Trademark";

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbCommand, "@CustomerID", DbType.Guid, Customer);
            if (InstrumentID.HasValue)
            {
                CurrentDatabase.AddInParameter(dbCommand, "@InstrumentID", DbType.Guid, InstrumentID.Value);
            }
            if (!string.IsNullOrEmpty(CateCode))
            {
                CurrentDatabase.AddInParameter(dbCommand, "@CateCode", DbType.String, CateCode + "%");
            }

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
        //取产品库所有品牌
        public DataTable QueryProductTrademark()
        {
            string sql = "";
            sql = @"SELECT distinct Trademark FROM Bas_Product where Trademark is not null";
            sql += " order by Trademark";

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(sql);
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
        //取产品大类（按品牌）
        public DataTable QueryProductCate()
        {
            string sql = "";
            sql = @"select CateCode,case when grade >2 then '|'+Replace(SPACE((grade-2)*4)+CateName,' ','-') else Replace(SPACE((grade-2)*4)+CateName,' ','-') end as CateName from Bas_Product_Cate where Grade >1 and CateName not like '%折扣' and CateType=1 order by CateCode";
           
            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(sql);
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
        //取产品大类（按功能）
        public DataTable QueryProductCateEx()
        {
            string sql = "";
            sql = @"select CateCode,case when grade >2 then '|'+Replace(SPACE((grade-2)*4)+CateName,' ','-') else Replace(SPACE((grade-2)*4)+CateName,' ','-') end as CateName from Bas_Product_Cate where Grade >1 and CateName not like '%折扣' and CateType=2 order by CateCode";

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(sql);
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

    }
}
